

ALTER   function fn_get_lineItem_reserved_lots (@line_item_no int, @sales_order_id int)
returns  @fn_results Table (ProcessStep_Name varchar(50), 
			lot_quantity int, 
			expected_date_out smalldatetime,
			lot_number varchar(50))
as 
begin
	declare @expected_date_out SMALLDATETIME
	declare @lot_Number varchar(50)
	declare @ps_type_name varchar(20)
	declare @process_step_id int
	declare @lot_quantity int
	
	declare @results Table (ProcessStep_Name varchar(50), lot_quantity int, expected_date_out smalldatetime, lot_number varchar(50))
	
	declare lotCursor CURSOR FOR
		select LRT_Lot_Number, LRT_Lot_Quantity, LRT_Expected_Date_Out
			from Lot_Reservation_Table where LRT_Line_Item_No = @line_item_no
			and LRT_SalesOrder_Id = @sales_order_id and LRT_Released = 1

	open lotCursor 
	fetch next from lotCursor into @lot_number, @lot_quantity, @expected_date_out				
	while @@fetch_status = 0
	begin			
		select @ps_type_name = Ps_type_name from Lot_Table,Process_Step WHERE Lot_Number= @lot_number and Lot_Current_Status='Running' and Lot_Table.ProcessStep_ID = Process_Step.ProcessStep_ID
		insert into @results (ProcessStep_Name, lot_quantity, expected_date_out, lot_number) values (@ps_type_name, @lot_quantity, @expected_date_out, @lot_number)
		fetch next from lotCursor into  @lot_number,@lot_quantity, @expected_date_out
	end
	close lotCursor
	deallocate lotCursor

	insert @fn_results select *from @results
	return
end



